﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BMCW.Infrastructure.Service;
using Dapper;
using Dapper.Extensions;
using Dapper.Extensions.Common;
using DapperDemo.Component.Model;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using YouWei.Infrastructure.Entity;

namespace DapperDemo.ConsoleTest
{
    class Program
    {
        static void Main(string[] args)
        {

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            var mySqlConn= new MySqlConnection(ConfigurationManager.ConnectionStrings["SmsCatConnectionString"].ConnectionString);

            SqlConnection connYouWeiLive = new SqlConnection(ConfigurationManager.ConnectionStrings["YouWeiLiveConnectionString"].ConnectionString);

            //SqlMapper.AddTypeHandlerImpl(typeof(DateTime),NullDateTimeHandler.Default,false);
            //var oFirst = conn.QueryFirstOrDefault<DapperTest>("select top 1 * from DapperTest");
            //var list = conn.Query<DapperTest>("select * from DapperTest");
            //var oNullDateTime = conn.QueryFirstOrDefault<DapperTest>("select top 1 * from DapperTest where UpdatedDate IS NULL");
            //if (oNullDateTime != null)
            //{
            //    oNullDateTime.UpdatedDate = DateTime.Now;
            //    var bUpdateSuccess = conn.Update(oNullDateTime);
            //}
            ////单条插入数据测试
            //DapperInsert(conn, new DapperTest()
            //{
            //    UserName = "test😐" + DateTime.Now.ToString("yyyyMMddHHmmssss"),
            //    CreatedDate = DateTime.Now,
            //    UpdatedDate = new DateTime(1900, 1, 1)
            //});
            //DapperUpdate(connYouWeiLive);
            ////原生批量插入数据测试
            //BatchInsertRaw();
            ////Dappper批量插入数据测试
            //BatchInsertDapper(conn);
            //Dappper批量更新数据测试
            //BatchUpdateDapper(conn);
            //TestQuerySingle();
            //TestQueryList();

            //MySqlInsert(mySqlConn);

            //TestDeletePredicate(conn);
            //TestTopFirstByOrder(conn);
            //TestTopFirstByMultiOrder(conn);
            //TestGetTopList(conn);
            //TestUpdateSpecialFields(conn);

            //TestStatisticFunctions(conn);
            //TestGetPageList(conn);

            TestQuerySingleField(conn);
            Console.ReadLine();
            
        }
        private static void TestQuerySingleField(SqlConnection conn)
        {
          
            Console.WriteLine("查询的值为：" + conn.QureySingleField<DapperTest, int>(m => m.UserId, m => m.UserName == "succ"));
            Console.WriteLine("查询的值为：" + conn.QureySingleField<DapperTest, int>(m => m.UserId, m => m.UserName == "succ"));
            Console.WriteLine("查询的值为：" + conn.QureySingleField<DapperTest, int>(m => m.UserId, m => m.UserName == "succ"));
        }
        private static void MySqlInsert(MySqlConnection conn)
        {

            var oSendMsg = new sms_send();
            oSendMsg.PortNum = -1;
            oSendMsg.smsNumber = "13436300382";
            oSendMsg.smsSubject = string.Empty;
            oSendMsg.smsContent = "hello ,test mysql insert";
            oSendMsg.smsType = 0;
            oSendMsg.PhoNum = "13552758135";
            oSendMsg.smsState = 0;
    
            int nSendID = 0;

            object o = conn.Insert(oSendMsg);
        }
        private static void TestQuerySingle()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            var oData=conn.QuerySingle<DapperTest>(m => m.UserId == 1);
            if (oData != null)
            {
                Console.WriteLine(nameof(TestQuerySingle) + "succcessed");
                Console.WriteLine(JsonConvert.SerializeObject(oData));
            }
            else
            {
                Console.WriteLine(nameof(TestQuerySingle) + "failed");
            }
        }

        private static void TestQueryList()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            var oData = conn.QueryList<DapperTest>(m => m.UserId > 1);
            if (oData.Count>0)
            {
                Console.WriteLine(nameof(TestQueryList) + "succcessed");
                Console.WriteLine(JsonConvert.SerializeObject(oData));
            }
            else
            {
                Console.WriteLine(nameof(TestQueryList) + "failed");
            }
        }

        private static void DapperInsert(SqlConnection conn, DapperTest oInfo)
        {
            conn.Insert(oInfo);
        }

        private static void DapperUpdate(SqlConnection conn)
        {
           
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚑X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 147, m => m.PlaceHolderText);
            conn.UpdateSpecialFields(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚒X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 148, m => m.PlaceHolderText);
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚓X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 149, m => m.PlaceHolderText);
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚔X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 150, m => m.PlaceHolderText);
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚕X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 151, m => m.PlaceHolderText);
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚖X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 152, m => m.PlaceHolderText);
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚗X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 153, m => m.PlaceHolderText);
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚘X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 154, m => m.PlaceHolderText);
            conn.UpdateSpecialFields<SmsPlaceHolder>(new SmsPlaceHolder()
            {
                PlaceHolderText = "北京BMW🚚X1车友群，群主驰骋"
            }, m => m.PlaceHolderID == 155, m => m.PlaceHolderText);
        }
        private static int RawInsert(DapperTest oInfo)
        {
            int nReturn = 0;
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string szSql = "INSERT INTO DapperTest(UserName, CreatedDate, UpdatedDate, TestDate1, TestDate2)" +
                           " VALUES(@UserName, @CreatedDate, @UpdatedDate, @TestDate1, @TestDate2);" +
                           " select SCOPE_IDENTITY() id";
            SqlCommand cmd = new SqlCommand(szSql,conn);
            cmd.Parameters.AddWithValue("@UserName", oInfo.UserName);
            cmd.Parameters.AddWithValue("@CreatedDate", ToNullDbDateTime(oInfo.CreatedDate));
            cmd.Parameters.AddWithValue("@UpdatedDate", ToNullDbDateTime(oInfo.UpdatedDate));
            cmd.Parameters.AddWithValue("@TestDate1", ToNullDbDateTime(oInfo.TestDate1));
            cmd.Parameters.AddWithValue("@TestDate2", ToNullDbDateTime(oInfo.TestDate2));
            
            using (conn)
            {
                try
                {
                    conn.Open();
                   var reader= cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (reader.Read())
                    {
                        nReturn = Convert.ToInt32(reader[0]);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return nReturn;
        }

        private static void BatchInsertRaw()
        {
            Stopwatch oWatch=new Stopwatch();
            oWatch.Start();
            for (int i = 0; i < 30000; i++)
            {
                var oInfo = new DapperTest()
                {
                    UserName = "test" + DateTime.Now.ToString("yyyyMMddHHmmssss"),
                    CreatedDate = DateTime.Now
                };
                RawInsert(oInfo);
            }
            oWatch.Stop();
            Console.WriteLine($"原生插入10000条测试数据用时{oWatch.ElapsedMilliseconds}ms");
        }
        private static void BatchInsertDapper(SqlConnection conn)
        {
         
            Stopwatch oWatch = new Stopwatch();
            oWatch.Start();
            for (int i = 0; i < 30000; i++)
            {
                var oInfo = new DapperTest()
                {
                    UserName = "test" + DateTime.Now.ToString("yyyyMMddHHmmssss"),
                    CreatedDate = DateTime.Now
                };
                DapperInsert(conn,oInfo);
            }
            oWatch.Stop();
            Console.WriteLine($"Dapper插入10000条测试数据用时{oWatch.ElapsedMilliseconds}ms");
        }

        private static void BatchUpdateDapper(SqlConnection conn)
        {

            Stopwatch oWatch = new Stopwatch();
            oWatch.Start();
            var oInfo = conn.QuerySingle<DapperTest>(m => m.UserId == 1);
            for (int i = 0; i < 30000; i++)
            {
                oInfo.UserName = "succ"+i;
                oInfo.CreatedDate=DateTime.MinValue;
                conn.Update(oInfo);
            }
            oWatch.Stop();
            Console.WriteLine($"Dapper插入10000条测试数据用时{oWatch.ElapsedMilliseconds}ms");
        }


        private static void Test()
        {
            var svUserService=new UserInforService();
            svUserService.AddOrUpdateWeiXinUserWhenSubscribe("oSeE6w1dTY30PRrsJypGaIlDhXOw", string.Empty);
        }
        /// <summary>
        /// 将日期时间类型转换为可供数据库授受的类型
        /// </summary>
        /// <param name="Date"></param>
        /// <returns></returns>
        public static object ToNullDbDateTime(DateTime Date)
        {
            object oDate = Date;
            //if (Date == DateTime.MinValue)
            //{
            //    oDate = DBNull.Value;
            //}
            if (Date.IsNullDateTime())
            {
                oDate = DBNull.Value;
            }
            return oDate;
        }
        /// <summary>
        /// 判断日期时间类型是否为空
        /// </summary>
        /// <param name="Date"></param>
        /// <returns></returns>
        public static bool IsNullDateTime(DateTime Date)
        {
            bool bReturn = Date == Convert.ToDateTime("1900-1-1") || Date == DateTime.MinValue;
            return bReturn;
        }


        public static void TestDeletePredicate(SqlConnection conn)
        {
            bool bSuccess=conn.Delete<DapperTest>(m => m.UserName == "test20190628111354");
            if (bSuccess)
            {
                Console.WriteLine("The data is deleted!");
            }
            else
            {
                Console.WriteLine("The data is lost!");
            }
        }

        public static void TestTopFirstByOrder(SqlConnection conn)
        {
            
             var oReturn=conn.GetTopFirstByOrder<DapperTest>(t=>t.CreatedDate>DateTime.Now.AddDays(-365),Sort.CreateSort<DapperTest>(m=>m.UserName,EnumSortDirection.Descending));
             Console.ReadLine();
        }

        public static void TestTopFirstByMultiOrder(SqlConnection conn)
        {

            var oReturn= conn.GetTopFirstByMultiOrder<DapperTest>(m => m.CreatedDate > DateTime.Now.AddDays(-365), new List<Sort>()
            {
               Sort.CreateSort<DapperTest>(m=>m.UserId,EnumSortDirection.Descending),
               Sort.CreateSort<DapperTest>(m=>m.UserName,EnumSortDirection.Descending),
            });
            Console.ReadLine();
        }

        public static void TestGetTopList(SqlConnection conn)
        {

            var list = conn.GetTopList<DapperTest>(m => m.CreatedDate > DateTime.Now.AddDays(-365),5, new List<Sort>()
            {
               Sort.CreateSort<DapperTest>(m=>m.UserId,EnumSortDirection.Descending),
               Sort.CreateSort<DapperTest>(m=>m.UserName,EnumSortDirection.Descending),
            });
            Console.ReadLine();
        }

        public static void TestUpdateSpecialFields(SqlConnection conn)
        {

            //bool bReturn=conn.UpdateSpecialFields<DapperTest>(new {UserName = "succ"}, m => m.UserName == "test20190628111355");

            var oInfo = new DapperTest()
            {
                UserName="succ20190226205300"
            };
            
            conn.UpdateSpecialFields<DapperTest>(oInfo,m => m.UserId == 30,m=>m.UserName);
            Console.ReadLine();
        }


        public static void TestStatisticFunctions(SqlConnection conn)
        {

            int nCount=conn.GetCount<DapperTest>(m => m.UserName == "succ");
            var longCount= conn.GetLongCount<DapperTest>(m => m.UserName == "succ");
            var bExist = conn.CheckIfExist<DapperTest>(m => m.UserName == "succ");
            var sum = conn.GetSum<DapperTest, int>(m => m.UserId, m => m.UserName == "succ");
            var sumFloat = conn.GetSum<DapperTest, float>(m => m.FloatValue, m => m.UserName == "succ");
            var sumDecimal = conn.GetSum<DapperTest, decimal>(m => m.DecimalValue, m => m.UserName == "succ");
            var sumDouble = conn.GetSum<DapperTest, double>(m => m.NumberValue, m => m.UserName == "succ");

            var avg = conn.GetAvg<DapperTest, int>(m => m.UserId, m => m.UserName == "succ");
            var avgFloat = conn.GetAvg<DapperTest, float>(m => m.FloatValue, m => m.UserName == "succ");
            var avgDecimal = conn.GetAvg<DapperTest, decimal>(m => m.DecimalValue, m => m.UserName == "succ");
            var avgDouble = conn.GetAvg<DapperTest, double>(m => m.NumberValue, m => m.UserName == "succ");

            Console.ReadLine();
        }

        public static void TestGetPageList(SqlConnection conn)
        {

            var pageData = conn.GetPageList<DapperTest>(m => m.UserName == "succ",
               m=>m.UserId,EnumSortDirection.Ascending, 1, 10);

            var pageData1 = conn.GetPageList<DapperTest>(m => m.UserName.Contains("1") && m.UserId > 0, m => m.UserId,
                EnumSortDirection.Descending, 1, 1000);



       
           // var pageData3 = conn.GetPageList<DapperTest>(m => m.UserId > 0, listSort, 1, 100);

            Console.ReadLine();
        }
    }
    public class NullDateTimeHandler : SqlMapper.TypeHandler<DateTime>
    {
        public static readonly NullDateTimeHandler Default = new NullDateTimeHandler();
        public override void SetValue(IDbDataParameter parameter, DateTime value)
        {
            if (IsNullDateTime(value))
            {
                parameter.DbType = DbType.DateTime;
                parameter.Value = DBNull.Value;
            }
        }

        public override DateTime Parse(object value)
        {
            if (value is DateTime)
            {
                if (IsNullDateTime((DateTime) value))
                {
                    return DateTime.MinValue;
                }
                else
                {
                    return Convert.ToDateTime(value);
                }
            }
            throw new Exception("Invalid DateTime Value!!!");
        }
        public  bool IsNullDateTime(DateTime Date)
        {
            bool bReturn = (Date== DateTime.MinValue || Date==Convert.ToDateTime("1900-01-01 00:00:00"));
            return bReturn;
        }
    }
   







}
